﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class DiscussAreaDAO
    {
        /// <summary>
        /// 存储新建讨论分区信息
        /// </summary>
        /// <param name="daTitle"></param>
        /// <param name="createTime"></param>
        /// <returns></returns>
        public int SaveDiscussAreaInfo(string daTitle, DateTime createTime)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into DiscussArea values(@daTitle, @createTime)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@daTitle", daTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@createTime", createTime));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据讨论分区编号查找分区信息
        /// </summary>
        /// <param name="daid"></param>
        /// <returns></returns>
        public DiscussArea findById(int daid)
        {
            DiscussArea discussArea = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussArea where DiscussAreaId = @daid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@daid", daid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    discussArea = new DiscussArea();
                    sqlDr.Read();
                    discussArea.DiscussAreaId = (int)sqlDr["DiscussAreaId"];
                    discussArea.DiscussAreaTitle = (string)sqlDr["DiscussAreaTitle"];
                    discussArea.DiscussAreaCreateTime = (DateTime)sqlDr["DiscussAreaCreateTime"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return discussArea;
        }

        /// <summary>
        /// 查找所有讨论分区信息
        /// </summary>
        /// <returns></returns>
        public List<DiscussArea> findALL()
        {
            List<DiscussArea> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussArea order by DiscussAreaId desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<DiscussArea>();
                    while (sqlDr.Read())
                    {
                        DiscussArea discussArea = new DiscussArea();
                        discussArea.DiscussAreaId = (int)sqlDr["DiscussAreaId"];
                        discussArea.DiscussAreaTitle = (string)sqlDr["DiscussAreaTitle"];
                        discussArea.DiscussAreaCreateTime = (DateTime)sqlDr["DiscussCreateTime"];
                        list.Add(discussArea);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 更新讨论分区信息
        /// </summary>
        /// <param name="daTitle"></param>
        /// <param name="daid"></param>
        /// <returns></returns>
        public int UpdateDiscussAreaInfo(string daTitle, DateTime lastWriteTime, int daid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update DiscussArea set DiscussAreaTitle = @daTitle, DiscussCreateTime = @lastWriteTime where DiscussAreaId = @daid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@daTitle", daTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@lastWriteTime", lastWriteTime));
                sqlCmd.Parameters.Add(new SqlParameter("@daid", daid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count; 
        }

        /// <summary>
        /// 根据分区编号删除此讨论区
        /// </summary>
        /// <param name="daid"></param>
        /// <returns></returns>
        public int DelDiscussAreaInfoById(int daid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from DiscussArea where DiscussAreaId = @daid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@daid", daid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open) 
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
